package 股东资料;

import com.mongodb.BasicDBObject;
import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import com.mongodb.client.model.UpdateOptions;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Main {
    static final String CompanyInfoFilePath = "D:\\测试临时文件\\choice抓取\\基金公司基本资料.xls";
    static MongoClient mongoClient = new MongoClient("192.168.1.173", 27017);
    static MongoDatabase mDatabase = mongoClient.getDatabase("基金choice抓取");
    static MongoCollection mCollection = mDatabase.getCollection("股东资料");

    public static void main(String[] args) throws IOException, InvalidFormatException {
        String[] companyNames = getCompanyNames();
        for (int i = 0; i < companyNames.length; i++) {
            Workbook workbook = WorkbookFactory.create(new File("D:\\测试临时文件\\choice抓取\\基金公司-股东资料\\"+(i+1) + ".xls"));
            Sheet sheet = workbook.getSheetAt(0);
            List<Map<String, String>> docList = getDocList(sheet);

            String companyName = companyNames[i];
            docList.forEach(doc -> {
                doc.put("基金公司名称",companyName);
                doc.put("_id", doc.get("基金公司名称") + "_" + doc.get("股东名称")+"_"+doc.get("统计日期")+"_"+
                        doc.get("持股数量（万股）")+"_"+doc.get("持股比例（%）")+"_"+doc.get("股东性质"));
                BasicDBObject query = new BasicDBObject().append("_id", doc.get("_id"));
                BasicDBObject obj = new BasicDBObject("$set", doc);
                mCollection.updateOne(query, obj, (new UpdateOptions()).upsert(true));
                System.out.println(doc);
            });
        }
    }

    public static List<Map<String, String>> getDocList(Sheet sheet){
        List<Map<String, String>> resList = new ArrayList<>();
        int row_index = 1;
        while (sheet.getRow(row_index) != null){
            Map<String, String> doc = new HashMap<>();
            Row row = sheet.getRow(row_index);
            doc.put("股东名称", row.getCell(0)+"");
            doc.put("统计日期", row.getCell(1)+"");
            doc.put("持股数量（万股）", row.getCell(2)+"");
            doc.put("持股比例（%）", row.getCell(3)+"");
            doc.put("股东性质", row.getCell(4)+"");
            resList.add(doc);
            row_index++;
        }
        return resList;
    }

    public static String[] getCompanyNames() throws IOException, InvalidFormatException {
        Workbook workbook = WorkbookFactory.create(new File(CompanyInfoFilePath));
        Sheet sheet = workbook.getSheetAt(0);

        String[] companyNames = new String[179];
        for (int i = 1;i < 180; i++) {
            companyNames[i-1] = sheet.getRow(i).getCell(0).getStringCellValue();
            System.out.println(companyNames[i-1]);
        }

        return companyNames;
    }
}
